package com.nononsenseapps.feeder.db.room

import android.database.Cursor
import androidx.paging.DataSource
import androidx.paging.PagingSource
import androidx.room.*
import com.nononsenseapps.feeder.db.FEED_ITEM
import com.nononsenseapps.feeder.db.FEED_ITEM.COL_ID
import com.nononsenseapps.feeder.db.FEED_ITEM.COL_PLAIN_SNIPPET
import com.nononsenseapps.feeder.db.FEED_ITEM.COL_TITLE
import com.nononsenseapps.feeder.db.entity.ATTENTION_NORMAL
import com.nononsenseapps.feeder.db.entity.FeedItem
import com.nononsenseapps.feeder.db.view.PreviewItem
import kotlinx.coroutines.flow.Flow
import org.joda.time.DateTime
import org.joda.time.DateTimeZone
import java.util.*

private const val LIKE_SEARCH = "LIKE '%' || :searchText || '%'"

@Dao
interface FeedItemDao {
    @Insert(onConflict = OnConflictStrategy.IGNORE)
    fun insertFeedItem(item: FeedItem): Long

    @Update
    fun updateFeedItem(item: FeedItem): Int

    @Delete
    fun deleteFeedItem(item: FeedItem)

    @Query("SELECT id FROM feed_items WHERE guid IS :guid ")
    fun isExistWithGuid(guid: String):Long?

    @Query("DELETE FROM feed_items WHERE id IS :itemId ")
    fun deleteFeedItem(itemId: Long)

    @Query("SELECT count(*) FROM feed_items  WHERE feed_id IS :feedId ")
    fun count(feedId: Long): Int

    @Query("""
        DELETE FROM feed_items WHERE id IN (
          SELECT id FROM feed_items
          WHERE feed_id IS :feedId and feed_items.${FEED_ITEM.COL_ATTENTION_LEVEL} <= $ATTENTION_NORMAL
          ORDER BY  create_date DESC
          LIMIT -1 OFFSET :keepCount
        )""")
    fun cleanItemsInFeed(feedId: Long, keepCount: Int)

    @Query("""
        UPDATE feed_items
        SET feed_id = :newFeedId
        WHERE id IN (
         SELECT id FROM feed_items
          WHERE feed_id IS :feedId and feed_items.${FEED_ITEM.COL_ATTENTION_LEVEL} <= $ATTENTION_NORMAL
          ORDER BY create_date DESC
          LIMIT -1 OFFSET :keepCount
        )""")
    fun backupItems(feedId: Long,newFeedId:Long = BACKUP_FEED_ID, keepCount: Int)

    @Query("""
        UPDATE feed_items
        SET feed_id = :newFeedId
        WHERE id IN (
         SELECT id FROM feed_items
          WHERE feed_id IS :feedId AND unread IS :unread and feed_items.${FEED_ITEM.COL_ATTENTION_LEVEL} <= $ATTENTION_NORMAL
          ORDER BY create_date DESC
          LIMIT -1 OFFSET :keepCount
        )""")
    fun backupReadItems(feedId: Long,newFeedId:Long = BACKUP_FEED_ID, keepCount: Int,unread: Boolean = false)

    @Query("SELECT * FROM feed_items WHERE guid IS :guid AND feed_id IS :feedId")
    fun loadFeedItem(guid: String, feedId: Long?): FeedItem?

    @Query("SELECT * FROM feed_items WHERE id IS :id")
    fun loadFeedItem(id: Long): FeedItem?

    @Query("""
        SELECT *
        FROM feed_items
        WHERE feed_items.feed_id = :feedId
        ORDER BY create_date DESC
        """)
    fun loadFeedItemsInFeed(feedId: Long): List<FeedItem>

    @Query("""
        SELECT *
        FROM feed_items
        WHERE feed_items.feed_id = :feedId
        ORDER BY create_date DESC
        LIMIT 100 OFFSET :page*100
        """)
    fun loadFeedItemsInFeedWithPage(feedId: Long,page:Int=0): List<FeedItem>

    @Query("""
        SELECT id
        FROM feed_items
        WHERE feed_items.full_text_sync = :fullTextSync
        ORDER BY create_date DESC
        """)
    fun loadFeedItemsWithFullTextSync(fullTextSync:Boolean=true):List<Long>


    @Query("""
        SELECT $feedItemColumnsWithFeed
        FROM feed_items
        LEFT JOIN feeds ON feed_items.feed_id = feeds.id
        WHERE feed_items.id IS :id
        """)
    fun loadFeedItemWithFeed(id: Long): FeedItemWithFeed

    @Query("""
        SELECT *
        FROM feed_item_view
        WHERE id IN (:itemIds)
        ORDER BY attention_level DESC,pub_date DESC
        """)
    fun loadLivePreviewsItemList(itemIds: List<Long>): List<PreviewItem>

    @Query("""
        SELECT *
        FROM feed_item_view
        WHERE feed_id IS :feedId AND create_date <= :startReadTime
        ORDER BY attention_level DESC,pub_date DESC
        """)
    fun loadLivePreviews(feedId: Long,startReadTime:DateTime): PagingSource<Int, PreviewItem>

   @Query("""
        SELECT *
        FROM feed_item_view
        WHERE tag IS :tag AND create_date <= :startReadTime
        ORDER BY attention_level DESC,pub_date DESC
        """)
    fun loadLivePreviews(tag: String,startReadTime:DateTime): PagingSource<Int, PreviewItem>

    @Query("""
        SELECT *
        FROM feed_item_view
        WHERE plain_title $LIKE_SEARCH  OR author $LIKE_SEARCH 
        ORDER BY attention_level DESC ,pub_date DESC
        """)
    fun loadLivePreviewsWithSearch(searchText: String): PagingSource<Int, PreviewItem>

   @Query("""
        SELECT *
        FROM feed_item_view
        WHERE (plain_title $LIKE_SEARCH  OR author $LIKE_SEARCH) AND unread IS :unread
        ORDER BY attention_level DESC,pub_date DESC
        """)
    fun loadLiveUnreadPreviewsWithSearch(searchText: String, unread: Boolean = true): PagingSource<Int, PreviewItem>

   @Query("""
        SELECT *
        FROM feed_item_view
        WHERE item_tags $LIKE_SEARCH 
        ORDER BY attention_level DESC,pub_date DESC
        """)
    fun loadLivePreviewsWithTag(searchText: String): PagingSource<Int, PreviewItem>

    @Query("""
        SELECT *
        FROM feed_item_view
        WHERE ( item_tags  $LIKE_SEARCH ) AND unread IS :unread
        ORDER BY attention_level DESC,pub_date DESC
        """)
    fun loadLiveUnreadPreviewsWithTag(searchText: String, unread: Boolean = true): PagingSource<Int, PreviewItem>

    @Query("""
        SELECT *
        FROM feed_item_view
        WHERE  unread IS :unread AND item_tags LIKE '%' || :tag1 || '%'  AND item_tags LIKE '%' || :tag2 || '%' 
        ORDER BY attention_level DESC,pub_date DESC
        """)
    fun loadLiveUnreadPreviewsWithTags(tag1: String,tag2: String, unread: Boolean = true): PagingSource<Int, PreviewItem>

    @Query("""
        SELECT *
        FROM feed_item_view
        WHERE  unread IS :unread AND item_tags LIKE '%' || :tag1 || '%'  AND item_tags LIKE '%' || :tag2 || '%' AND item_tags LIKE '%' || :tag3 || '%'
        ORDER BY attention_level DESC,pub_date DESC
        """)
    fun loadLiveUnreadPreviewsWithTags(tag1: String,tag2: String,tag3: String, unread: Boolean = true): PagingSource<Int, PreviewItem>

    @Query("""
        SELECT *
        FROM feed_item_view
        WHERE  unread IS :unread AND item_tags LIKE '%' || :tag1 || '%'  AND item_tags LIKE '%' || :tag2 || '%' AND item_tags LIKE '%' || :tag3 || '%'  AND item_tags LIKE '%' || :tag4 || '%'
        ORDER BY attention_level DESC,pub_date DESC
        """)
    fun loadLiveUnreadPreviewsWithTags(tag1: String,tag2: String,tag3: String,tag4: String, unread: Boolean = true): PagingSource<Int, PreviewItem>

    @Query("""
        SELECT *
        FROM feed_item_view
        WHERE create_date <= :startReadTime
        ORDER BY attention_level DESC,pub_date DESC
        """)
    fun loadLivePreviews(startReadTime: DateTime): PagingSource<Int, PreviewItem>

    @Query("""
        SELECT *
        FROM feed_item_view
        WHERE feed_id IS :feedId AND unread IS :unread AND create_date <= :startReadTime
        ORDER BY attention_level DESC,pub_date DESC
        """)
    fun loadLiveUnreadPreviews(feedId: Long?,startReadTime:DateTime, unread: Boolean = true): PagingSource<Int, PreviewItem>

    @Query("""
        SELECT *
        FROM feed_item_view
        WHERE tag IS :tag AND unread IS :unread AND create_date <= :startReadTime
        ORDER BY attention_level DESC,pub_date DESC
        """)
    fun loadLiveUnreadPreviews(tag: String,startReadTime:DateTime, unread: Boolean = true): PagingSource<Int, PreviewItem>

    @Query("""
        SELECT *
        FROM feed_item_view
        WHERE unread IS :unread AND create_date <= :startReadTime
        ORDER BY attention_level DESC,pub_date DESC
        """)
    fun loadLiveUnreadPreviews(startReadTime:DateTime,unread: Boolean = true): PagingSource<Int, PreviewItem>


    @Query("""
        SELECT *
        FROM feed_item_view
        WHERE id IN (SELECT feed_item_id FROM feed_item_preload_item WHERE is_enclosure = 1) and unread IS :unread
        ORDER BY attention_level DESC,pub_date DESC
        """)
    fun loadLiveDownLoadUnreadPreviews(unread: Boolean = true): PagingSource<Int, PreviewItem>

    @Query("""
        SELECT *
        FROM feed_item_view
        WHERE id IN (SELECT feed_item_id FROM feed_item_preload_item WHERE is_enclosure = 1)
        ORDER BY attention_level DESC,pub_date DESC
        """)
    fun loadLiveDownLoadPreviews(): PagingSource<Int, PreviewItem>

    @Query("""
        SELECT *
        FROM feed_item_view
        WHERE read_history IS NOT null
        ORDER BY read_history DESC
        """)
    fun loadReadHistoryPreviews(): PagingSource<Int, PreviewItem>

    @Query("""
        SELECT *
        FROM feed_item_view
        WHERE item_feature IS :featureId
        ORDER BY pub_date DESC
        """)
    fun loadLivePreviewsWithFeature(featureId: Long):  PagingSource<Int, PreviewItem>

    @Query("""
        SELECT *
        FROM feed_item_view
        WHERE feed_id IS :feedId and pub_date >= :firstDay and pub_date <= :lastDay
        ORDER BY attention_level DESC,pub_date DESC
        """)
    fun queryByDateWithFeed(feedId: Long,firstDay: DateTime, lastDay: DateTime): PagingSource<Int, PreviewItem>

    @Query("""
        SELECT *
        FROM feed_item_view
        WHERE  pub_date >= :firstDay and pub_date <= :lastDay
        ORDER BY attention_level DESC,pub_date DESC
        """)
    fun queryByDate(firstDay: DateTime, lastDay: DateTime): PagingSource<Int, PreviewItem>

    @Query("""
        SELECT *
        FROM feed_item_view
        WHERE unread IS :unread and pub_date >= :firstDay and pub_date <= :lastDay
        ORDER BY attention_level DESC,pub_date DESC
        """)
    fun queryByDateWithUnRead(firstDay: DateTime, lastDay: DateTime,unread: Boolean=true): PagingSource<Int, PreviewItem>

    @Query("""
        SELECT *
        FROM feed_item_view
        WHERE feed_id IS :feedId and unread IS :unread and pub_date >= :firstDay and pub_date <= :lastDay
        ORDER BY attention_level DESC,pub_date DESC
        """)
    fun queryByDateWithFeedAndUnRead(feedId: Long,firstDay: DateTime, lastDay: DateTime,unread: Boolean=true): PagingSource<Int, PreviewItem>


    @Query("""
        SELECT *
        FROM feed_item_view
        WHERE item_feature IS :featureId
        ORDER BY pub_date DESC
        """)
    fun loadLivePreviewsWithFeatureFlow(featureId: Long):  Flow<List<PreviewItem>>

    @Query("""
        SELECT *
        FROM feed_item_view
        WHERE feed_id IS :feedId
        ORDER BY pub_date DESC
        """)
    fun loadFeedLivePreviews(feedId: Long): Flow<List<PreviewItem>>


    @Query("""
        SELECT $feedItemColumnsWithFeed
        FROM feed_items
        LEFT JOIN feeds ON feed_items.feed_id = feeds.id
        WHERE feed_id IN (:feedIds) AND notified IS 0 AND unread IS 1
        """)
    fun loadItemsToNotify(feedIds: List<Long>): List<FeedItemWithFeed>

    @Query("UPDATE feed_items SET unread = 0")
    fun markAllAsRead()

    @Query("UPDATE feed_items SET unread = 0  and pub_date <= :beforeTime ")
    fun markAllAsRead(beforeTime:DateTime)

    @Query("UPDATE feed_items SET unread = 0 WHERE feed_id IS :feedId and pub_date <= :beforeTime ")
    fun markAllAsRead(feedId: Long,beforeTime:DateTime)

    @Query("UPDATE feed_items SET unread = 0 WHERE feed_id IS :feedId ")
    fun markAllAsRead(feedId: Long)

    @Query("""
        UPDATE feed_items
        SET unread = 0
        WHERE id IN (
          SELECT feed_items.id
          FROM feed_items
          LEFT JOIN feeds ON feed_items.feed_id = feeds.id
          WHERE tag IS :tag
        )""")
    fun markAllAsRead(tag: String)

    @Query("""
        UPDATE feed_items
        SET unread = 0
        WHERE id IN (
          SELECT feed_items.id
          FROM feed_items
          LEFT JOIN feeds ON feed_items.feed_id = feeds.id
          WHERE tag IS :tag
        ) and pub_date <= :beforeTime """)
    fun markAllAsRead(tag: String,beforeTime:DateTime)

    @Query("UPDATE feed_items SET unread = :unread WHERE id IN (:ids)")
    fun markAllAsRead(ids: List<Long>, unread: Boolean = false)

    @Query("UPDATE feed_items SET unread = :unread WHERE id IS :id")
    fun markAsRead(id: Long, unread: Boolean = false)

    @Query("UPDATE feed_items SET feed_id = $FAVORITE_FEED_ID,pub_date=:date,unread = :unread WHERE id IN (:ids) and feed_id != $FAVORITE_FEED_ID")
    fun markAllAsFavorite(ids: List<Long> ,date:DateTime=DateTime.now(DateTimeZone.UTC),unread: Boolean=true)

    @Query("UPDATE feed_items SET feed_id = $FAVORITE_FEED_ID,pub_date=:date,item_tags=:tag,unread = :unread WHERE id IS :id and feed_id != $FAVORITE_FEED_ID")
    fun markAsFavorite(id: Long, tag: String, date:DateTime=DateTime.now(DateTimeZone.UTC),unread: Boolean=true)

    @Query("UPDATE feed_items SET notified = :notified WHERE id IN (:ids)")
    fun markAsNotified(ids: List<Long>, notified: Boolean = true)

    @Query("UPDATE feed_items SET read_history=:date,unread = 0 WHERE id IS :id")
    fun markAsReadHistory(id: Long,date:DateTime=DateTime.now(DateTimeZone.UTC))

    @Query("UPDATE feed_items SET notified = :notified WHERE id IS :id")
    fun markAsNotified(id: Long, notified: Boolean = true)

    @Query("""
        UPDATE feed_items
        SET notified = :notified
        WHERE id IN (
          SELECT feed_items.id
          FROM feed_items
          LEFT JOIN feeds ON feed_items.feed_id = feeds.id
          WHERE tag IS :tag
        )""")
    fun markTagAsNotified(tag: String, notified: Boolean = true)

    @Query("UPDATE feed_items SET notified = :notified")
    fun markAllAsNotified(notified: Boolean = true)

    @Query("UPDATE feed_items SET unread = 0, notified = 1,read_history=:date WHERE id IS :id")
    fun markAsReadAndNotified(id: Long,date:DateTime=DateTime.now(DateTimeZone.UTC))

    @Query("SELECT description FROM feed_items WHERE id IS :id")
    fun loadFeedItemContent(id: Long): String?

    @Query("UPDATE feed_items SET item_tags = :tagStr WHERE id IS :id")
    fun saveItemTag(id: Long?, tagStr: String)

    @Query("UPDATE feed_items SET item_feature = :featureId WHERE id IS :id")
    fun setFeedItemFeature(id: Long, featureId: Long?)

    @Query("DELETE FROM feed_items WHERE feed_id > 0  ")
    fun markAllAsDelete()

    @Query("DELETE FROM feed_items WHERE id IN (:ids)  ")
    fun markAllAsDelete(ids:List<Long>)

    @Query("UPDATE feed_items SET unread = 1 WHERE feed_id > 0 ")
    fun markAllAsUnRead()

    @Query("UPDATE feed_items SET unread = 1 WHERE id IN (:ids)")
    fun markAllAsUnRead(ids: List<Long>)

    @Query("DELETE FROM feed_items WHERE feed_id = :feedId  ")
    fun deleteAllInFeed(feedId: Long)

    @Query("UPDATE feed_items SET feed_id = :toFeedId WHERE id = :id")
    fun moveItemToFeed(id: Long, toFeedId: Long)

    @Query("UPDATE feed_items SET feed_id = :toFeedId WHERE feed_id = :frommFeedId")
    fun moveFeedToFeed(frommFeedId: Long, toFeedId: Long)

    @Query("UPDATE feed_items SET image_url = :imageUrl,image_count = :imageCount WHERE id IS :id")
    fun updateImageUrl(id: Long, imageUrl:String="", imageCount:Int=0)

    @Query("UPDATE feed_items SET attention_level = :attentionLevel WHERE id = :id")
    fun markItemAttentionLevel(id: Long, attentionLevel: Int)

    @Query("""
        SELECT item_tags
        FROM feed_items
        WHERE item_tags $LIKE_SEARCH 
        LIMIT 10
        """)
    fun loadSimileTagWithTag(searchText: String): List<String>

    @Query("UPDATE feed_items SET full_text_sync = :fullTextSync WHERE id = :id")
    fun markItemFullTextSync(id: Long,fullTextSync:Boolean=true)

    @Query("""
        SELECT *
        FROM feed_items
        WHERE (plain_title $LIKE_SEARCH  OR author $LIKE_SEARCH)
        ORDER BY attention_level DESC,pub_date DESC
        LIMIT 50 OFFSET 50*:pageNumber
        """)
    fun search(searchText: String, pageNumber: Int): List<FeedItem>

    @Query("UPDATE feed_items SET description = :tranStr , full_text_sync = 0 WHERE id IS :id")
    fun loadFullPageText(id: Long, tranStr: String)


    @Query(
        """
        SELECT $COL_ID as id, $COL_TITLE as title, $COL_PLAIN_SNIPPET as text
        FROM feed_items
        ORDER BY  pub_date DESC
        """
    )
    fun loadFeedItemsForContentProvider(): Cursor

    @Query(
        """
        SELECT $COL_ID as id, $COL_TITLE as title, $COL_PLAIN_SNIPPET as text
        FROM feed_items
        WHERE feed_items.feed_id = :feedId
        ORDER BY  pub_date DESC
        """
    )
    fun loadFeedItemsInFeedForContentProvider(feedId: Long): Cursor

    @Query("SELECT id FROM feed_items WHERE $COL_TITLE IS :title ")
    fun isExistWithTitle(title: String): Long?

    @Query("UPDATE feed_items SET unread = 1,description=:bodyText WHERE id IS :id")
    fun updateFeedItemBody(id: Long, bodyText: String)


}

fun FeedItemDao.upsert(item: FeedItem): Long = when (item.id > ID_UNSET) {
    true -> {
        updateFeedItem(item)
        item.id
    }
    false -> {
        val id=insertFeedItem(item)
        item.id=id
        id
    }
}
